const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取今日课程
router.get('/today', auth, async (req, res) => {
  try {
    // 获取用户ID，支持从请求参数或认证信息中获取
    const userId = req.query.userId || req.user.id;
    
    if (!userId) {
      return res.status(400).json({ 
        success: false,
        message: '缺少用户ID参数' 
      });
    }

    // 获取用户角色 - 修改查询，同时支持id和userId(学号)查询
    const [userRows] = await pool.query(
      'SELECT id, role FROM users WHERE id = ? OR userId = ?',
      [userId, userId]
    );

    if (userRows.length === 0) {
      return res.status(404).json({ 
        success: false,
        message: '用户不存在' 
      });
    }

    const userRole = userRows[0].role;
    const actualUserId = userRows[0].id; // 使用数据库中的实际ID
    
    // 获取当前是星期几（1-7，对应周一到周日）
    const today = new Date();
    const weekday = today.getDay() === 0 ? 7 : today.getDay();
    
    console.log('获取今日课程，用户ID:', userId, '实际ID:', actualUserId, '角色:', userRole, '星期:', weekday);

    // 使用courses表获取课程数据
    let courses = [];
    const now = new Date();
    const hours = now.getHours();
    const minutes = now.getMinutes();
    
    if (userRole === 'teacher') {
      // 查询教师今日课程
      const [teacherCourses] = await pool.query(
        `SELECT 
          c.id, 
          c.name as courseName,
          cl.className as className,
          CONCAT(b.name, '-', c.classroomId) as location,
          c.startTime,
          c.endTime
        FROM 
          courses c
        LEFT JOIN 
          course_class_relation ccr ON c.id = ccr.courseId
        LEFT JOIN 
          classes cl ON ccr.classId = cl.id
        LEFT JOIN 
          buildings b ON c.buildingId = b.id
        WHERE 
          c.teacherId = ? AND c.weekday = ?`,
        [actualUserId, weekday]
      );
      
      // 处理查询结果，添加课程状态
      courses = teacherCourses.map(course => {
        // 添加空值检查，确保startTime和endTime有值
        const startTime = course.startTime || '00:00:00';
        const endTime = course.endTime || '00:00:00';
        const startHour = parseInt(startTime.split(':')[0]);
        const endHour = parseInt(endTime.split(':')[0]);
        
        return {
          id: course.id,
          courseName: course.courseName,
          className: course.className || '未分配班级',
          location: course.location || '未分配教室',
          startTime: startTime.substring(0, 5),
          endTime: endTime.substring(0, 5),
          status: hours > endHour ? '已结束' : (hours >= startHour ? '进行中' : '未开始')
        };
      });
    } else if (userRole === 'student') {
      // 查询学生今日课程
      const [studentCourses] = await pool.query(
        `SELECT 
          c.id, 
          c.name as courseName,
          cl.className as className,
          CONCAT(b.name, '-', c.classroomId) as location,
          c.startTime,
          c.endTime,
          u.name as teacherName
        FROM 
          courses c
        JOIN 
          course_student_relation csr ON c.id = csr.courseId
        LEFT JOIN 
          course_class_relation ccr ON c.id = ccr.courseId
        LEFT JOIN 
          classes cl ON ccr.classId = cl.id
        LEFT JOIN 
          buildings b ON c.buildingId = b.id
        LEFT JOIN 
          users u ON c.teacherId = u.id
        WHERE 
          csr.studentId = ? AND c.weekday = ?`,
        [actualUserId, weekday]
      );
      
      // 处理查询结果，添加课程状态
      courses = studentCourses.map(course => {
        // 添加空值检查，确保startTime和endTime有值
        const startTime = course.startTime || '00:00:00';
        const endTime = course.endTime || '00:00:00';
        const startHour = parseInt(startTime.split(':')[0]);
        const endHour = parseInt(endTime.split(':')[0]);
        
        return {
          id: course.id,
          courseName: course.courseName,
          className: course.className || '未分配班级',
          location: course.location || '未分配教室',
          startTime: startTime.substring(0, 5),
          endTime: endTime.substring(0, 5),
          teacherName: course.teacherName || '未分配教师',
          status: hours > endHour ? '已结束' : (hours >= startHour ? '进行中' : '未开始')
        };
      });
    }

    // 修改返回格式，直接返回数组而不是包装在data字段中
    res.json(courses);
  } catch (error) {
    console.error('获取今日课程失败:', error);
    res.status(500).json({ 
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

// 获取学生课程表
router.get('/timetable/student', auth, async (req, res) => {
  try {
    // 检查用户是否为学生
    if (req.user.role !== 'student') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const studentId = req.user.id;
    let { week, semester } = req.query;
    
    console.log('获取学生课表，参数:', { studentId, week, semester });
    
    // 格式化学期，参考admin-courses.js的逻辑
    let formattedSemester = semester;
    if (semester && (semester === '1' || semester === '2' || semester === 1 || semester === 2)) {
      // 如果学期只是简单的1或2，则格式化为正确的格式
      const currentYear = new Date().getFullYear();
      if (semester == '1' || semester == 1) {
        formattedSemester = `${currentYear}-${currentYear+1}-1`;
      } else {
        formattedSemester = `${currentYear-1}-${currentYear}-2`;
      }
      console.log('学生端格式化学期:', formattedSemester);
    }
  
    const [studentInfo] = await pool.query(`
      SELECT classId FROM users WHERE id = ? AND role = 'student'
    `, [studentId]);
    
    if (studentInfo.length === 0 || !studentInfo[0].classId) {
      return res.status(404).json({ message: '未找到学生所在班级' });
    }
    
    const classId = studentInfo[0].classId;
    console.log('学生所在班级ID:', classId);
    
    // 添加调试日志，查看SQL参数
    console.log('查询课表参数:', { classId, week, formattedSemester });
    
    // 简化查询语句，只查询学生所在班级的课程，不再使用majorId
    const [timetable] = await pool.query(`
      SELECT t.id, t.classId, t.courseId, t.teacherId, t.roomId, t.day, t.period, 
             t.startWeek, t.endWeek, t.semester,
             c.name as courseName, c.credits,
             u.name as teacherName
      FROM timetables t
      JOIN courses c ON t.courseId = c.id
      LEFT JOIN users u ON t.teacherId = u.id
      WHERE t.classId = ?
      AND (? BETWEEN t.startWeek AND t.endWeek OR ? = 0)
      AND (t.semester = ? OR t.semester IS NULL)
    `, [classId, week, week, formattedSemester]);
    
    console.log(`查询到 ${timetable.length} 条课表记录`);
    
    // 处理结果，添加默认的教室名称
    const processedTimetable = timetable.map(item => ({
      ...item,
      roomName: item.roomId ? `教室${item.roomId}` : '未分配教室'
    }));
    
    res.json(processedTimetable);
  } catch (error) {
    console.error('获取学生课程表失败:', error);
    res.status(500).json({ message: '服务器错误', error: error.message });
  }
});

// 获取学生考勤统计
router.get('/attendance/stats', auth, async (req, res) => {
  try {
    const userId = req.query.userId || req.user.id;
    
    if (!userId) {
      return res.status(400).json({ 
        success: false, 
        message: '缺少用户ID参数' 
      });
    }
    
    // 先查询用户实际ID
    const [userRows] = await pool.query(
      'SELECT id FROM users WHERE id = ? OR userId = ?',
      [userId, userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }
    
    const actualUserId = userRows[0].id;
    
    // 查询学生考勤统计
    const [attendanceStats] = await pool.query(`
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as attended,
        SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN status = 'leave' THEN 1 ELSE 0 END) as \`leave\`
      FROM attendance
      WHERE studentId = ?
    `, [actualUserId]);
    
    if (attendanceStats.length === 0) {
      return res.status(404).json({
        success: false,
        message: '未找到考勤记录'
      });
    }
    
    const stats = attendanceStats[0];
    
    // 计算出勤率 (出勤 + 迟到) / 总数
    const attendanceRate = stats.total > 0 
      ? (((parseInt(stats.attended) + parseInt(stats.late)) / stats.total) * 100).toFixed(1) + '%' 
      : '0%';
    
    res.json({
      success: true,
      data: {
        ...stats,
        attendanceRate
      }
    });
  } catch (error) {
    console.error('获取学生考勤统计失败:', error);
    res.status(500).json({ 
      success: false, 
      message: '服务器错误',
      error: error.message
    });
  }
});

// 获取学期列表
router.get('/semesters', auth, async (req, res) => {
  try {
    // 查询所有不同的学期
    const [semesters] = await pool.query(`
      SELECT DISTINCT semester FROM courses
      WHERE semester IS NOT NULL
      ORDER BY semester DESC
    `);
    
    // 处理学期数据
    const formattedSemesters = semesters.map(item => {
      return { id: item.semester, name: item.semester };
    });
    
    res.json(formattedSemesters);
  } catch (error) {
    console.error('获取学期列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 添加一个新的路由用于获取学生完整课表（包含所有周次）
router.get('/timetable/full', auth, async (req, res) => {
  try {
    const { semester } = req.query;
    const studentId = req.query.userId || req.user.id;
    
    if (!studentId) {
      return res.status(400).json({ 
        success: false, 
        message: '缺少用户ID参数' 
      });
    }
    
    // 先查询用户实际ID和班级ID
    const [userRows] = await pool.query(
      'SELECT id, classId FROM users WHERE id = ? OR userId = ? AND role = "student"',
      [studentId, studentId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在或不是学生'
      });
    }
    
    const actualUserId = userRows[0].id;
    const classId = userRows[0].classId;
    
    if (!classId) {
      return res.status(400).json({
        success: false,
        message: '学生未分配班级'
      });
    }
    
    // 简化查询语句，只查询学生所在班级的课程
    let query = `
      SELECT 
        t.id,
        c.name as courseName,
        u.name as teacherName,
        t.roomId as location,
        t.day,
        t.period,
        t.startWeek,
        t.endWeek,
        t.semester
      FROM 
        timetables t
      JOIN 
        courses c ON t.courseId = c.id
      LEFT JOIN 
        users u ON t.teacherId = u.id
      WHERE 
        t.classId = ?
    `;
    
    const queryParams = [classId];
    
    if (semester) {
      query += ' AND t.semester = ?';
      queryParams.push(semester);
    }
    
    query += ' ORDER BY t.day, t.period';
    
    const [timetableRows] = await pool.query(query, queryParams);
    
    // 处理课程数据，按周几分组
    const weekdays = [[], [], [], [], [], [], []]; // 周一到周日
    
    // 定义课程时间段
    const periodTimes = [
      { start: '08:00', end: '08:45' }, // 第1节
      { start: '08:55', end: '09:40' }, // 第2节
      { start: '10:00', end: '10:45' }, // 第3节
      { start: '10:55', end: '11:40' }, // 第4节
      { start: '14:00', end: '14:45' }, // 第5节
      { start: '14:55', end: '15:40' }, // 第6节
      { start: '16:00', end: '16:45' }, // 第7节
      { start: '16:55', end: '17:40' }, // 第8节
      { start: '19:00', end: '19:45' }, // 第9节
      { start: '19:55', end: '20:40' }  // 第10节
    ];
    
    timetableRows.forEach(course => {
      if (course.day >= 0 && course.day <= 6) {
        const dayIndex = course.day;
        const periodIndex = course.period;
        
        // 确保时间段存在
        const startTime = periodIndex < periodTimes.length ? periodTimes[periodIndex].start : '00:00';
        const endTime = periodIndex < periodTimes.length ? periodTimes[periodIndex].end : '00:00';
        
        weekdays[dayIndex].push({
          id: course.id,
          courseName: course.courseName,
          teacherName: course.teacherName,
          location: course.location ? `教室${course.location}` : '未指定地点',
          startTime: startTime,
          endTime: endTime,
          period: periodIndex,
          startWeek: course.startWeek,
          endWeek: course.endWeek,
          semester: course.semester
        });
      }
    });
    
    res.json({
      success: true,
      data: {
        weekdays,
        semester: semester || '当前学期'
      }
    });
  } catch (error) {
    console.error('获取完整课表失败:', error);
    res.status(500).json({ 
      success: false, 
      message: '服务器错误',
      error: error.message
    });
  }
});

// 获取学生课程列表
router.get('/student', async (req, res) => {
  try {
    const { userId } = req.query;
    
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '缺少用户ID参数'
      });
    }
    
    // 查询学生ID和班级ID
    const [userRows] = await pool.query(
      'SELECT id, classId FROM users WHERE userId = ?',
      [userId]
    );
    
    if (userRows.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }
    
    const studentId = userRows[0].id;
    const classId = userRows[0].classId;
    
    // 使用timetables表查询课程信息
    const [timetables] = await pool.query(`
      SELECT 
        t.id as timetableId,
        t.courseId,
        c.name,
        u.name as teacherName,
        t.roomId as location,
        t.day as weekday,
        t.period,
        t.startWeek,
        t.endWeek,
        t.semester
      FROM 
        timetables t
      JOIN 
        courses c ON t.courseId = c.id
      LEFT JOIN 
        users u ON t.teacherId = u.id
      WHERE 
        t.classId = ?
      ORDER BY 
        t.day, t.period
    `, [classId]);
    
    // 获取今天是星期几
    const today = new Date();
    const weekday = today.getDay(); // 0是星期日，1-6是星期一到星期六
    
    // 处理课程数据，标记今日课程
    const processedCourses = timetables.map(course => {
      // 将数据库中的星期几(1-7)转换为JS的星期几(0-6)
      const courseWeekday = course.weekday === 7 ? 0 : course.weekday;
      
      return {
        id: course.courseId,
        name: course.name,
        teacherName: course.teacherName || '未分配教师',
        location: course.location ? `教室${course.location}` : '未分配教室',
        weekday: course.weekday,
        period: course.period,
        startWeek: course.startWeek,
        endWeek: course.endWeek,
        semester: course.semester,
        isToday: courseWeekday === weekday
      };
    });
    
    res.json({
      success: true,
      courses: processedCourses
    });
    
  } catch (error) {
    console.error('获取学生课程列表失败:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误',
      error: error.message
    });
  }
});

module.exports = router;